package com.yeskery.nut.extend.jdbc;

import com.yeskery.nut.util.StringUtils;

import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * jdbc SQL 构建类
 * @author sprout
 * 2025-09-28 17:40
 */
public final class JdbcSqlBuilder {

    /** Jdbc Template */
    private final JdbcTemplate jdbcTemplate;

    /** 查询列 */
    private final List<String> queryColumns = new ArrayList<>();

    /** 分组列 */
    private final List<String> groupByColumns = new ArrayList<>();

    /** 条件 */
    private final DefaultSqlChainOperate querySqlChainOperate = new DefaultSqlChainOperate(this);

    /** 过滤 */
    private final DefaultSqlChainOperate havingSqlChainOperate = new DefaultSqlChainOperate(this);

    /** 设置 */
    private final DefaultSqlChainOperate setSqlChainOperate = new DefaultSqlChainOperate(this);

    /** 排序列 */
    private final List<String> orderByColumns = new ArrayList<>();

    /** 连表 */
    private final List<String> joins = new ArrayList<>();

    /** 表名 */
    private String tableName;

    /** 结束的SQL */
    private String lastSql;

    /**
     * 构造方法
     * @param jdbcTemplate JdbcTemplate
     */
    JdbcSqlBuilder(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 构建SQL查询列
     * @param columns 查询列名
     * @return SQL查询列
     */
    public JdbcSqlBuilder select(String... columns) {
        this.queryColumns.addAll(Arrays.asList(columns));
        return this;
    }

    /**
     * 构建SQL查询所有列
     * @return SQL查询列
     */
    public JdbcSqlBuilder selectAll() {
        this.queryColumns.clear();
        this.queryColumns.add("*");
        return this;
    }

    /**
     * 设置表名
     * @param tableName 表名
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder from(String tableName) {
        this.tableName = tableName;
        return this;
    }

    /**
     * 排序
     * @param column 排序列名
     * @param asc 是否升序
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder orderBy(String column, boolean asc) {
        this.orderByColumns.add(column + " " + (asc ? "ASC" : "DESC"));
        return this;
    }

    /**
     * 升序排序
     * @param columns 排序列名
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder orderByAsc(String... columns) {
        this.orderByColumns.addAll(Stream.of(columns).map(column -> column + " ASC").collect(Collectors.toList()));
        return this;
    }

    /**
     * 降序排序
     * @param columns 排序列名
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder orderByDesc(String... columns) {
        this.orderByColumns.addAll(Stream.of(columns).map(column -> column + " DESC").collect(Collectors.toList()));
        return this;
    }

    /**
     * 排序
     * @param columnDescriptions 排序列名
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder orderBy(String... columnDescriptions) {
        this.orderByColumns.addAll(Arrays.asList(columnDescriptions));
        return this;
    }

    /**
     * 分组
     * @param columns 分组列名
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder groupBy(String... columns) {
        this.groupByColumns.addAll(Arrays.asList(columns));
        return this;
    }

    /**
     * 查询条件
     * @return Sql链操作接口
     */
    public SqlChainOperate where() {
        return querySqlChainOperate;
    }

    /**
     * 过滤条件
     * @return Sql链操作接口
     */
    public SqlChainOperate having() {
        return havingSqlChainOperate;
    }

    /**
     * 设置
     * @return Sql链操作接口
     */
    public SqlChainOperate set() {
        return setSqlChainOperate;
    }

    /**
     * 连表
     * @param joinTableName 连表表名
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder join(String joinTableName) {
        joins.add("JOIN " + joinTableName);
        return this;
    }

    /**
     * 连表
     * @param joinTableName 连表表名
     * @param onCondition 连表条件
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder join(String joinTableName, String onCondition) {
        joins.add("JOIN " + joinTableName + " ON " + onCondition);
        return this;
    }

    /**
     * 左连接
     * @param joinTableName 连表表名
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder leftJoin(String joinTableName) {
        joins.add("LEFT JOIN " + joinTableName);
        return this;
    }

    /**
     * 左连接
     * @param joinTableName 连表表名
     * @param onCondition 连表条件
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder leftJoin(String joinTableName, String onCondition) {
        joins.add("LEFT JOIN " + joinTableName + " ON " + onCondition);
        return this;
    }

    /**
     * 右连接
     * @param joinTableName 连表表名
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder rightJoin(String joinTableName) {
        joins.add("RIGHT JOIN " + joinTableName);
        return this;
    }

    /**
     * 右连接
     * @param joinTableName 连表表名
     * @param onCondition 连表条件
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder rightJoin(String joinTableName, String onCondition) {
        joins.add("RIGHT JOIN " + joinTableName + " ON " + onCondition);
        return this;
    }

    /**
     * 内连接
     * @param joinTableName 连表表名
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder innerJoin(String joinTableName) {
        joins.add("INNER JOIN " + joinTableName);
        return this;
    }

    /**
     * 内连接
     * @param joinTableName 连表表名
     * @param onCondition 连表条件
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder innerJoin(String joinTableName, String onCondition) {
        joins.add("INNER JOIN " + joinTableName + " ON " + onCondition);
        return this;
    }

    /**
     * 结束的SQL
     * @param lastSql 结束的SQL
     * @return JdbcSqlBuilder
     */
    public JdbcSqlBuilder lastSql(String lastSql) {
        this.lastSql = lastSql;
        return this;
    }

    /**
     * 获取查询SQL
     * @return 查询SQL
     */
    public String getSelectSql() {
        if (StringUtils.isBlank(tableName)) {
            throw new JdbcException("Table Name Not Set Yet.");
        }
        StringBuilder sqlBuilder = new StringBuilder("SELECT ");
        if (queryColumns.isEmpty()) {
            throw new JdbcException("Query Columns Not Set Yet.");
        }
        sqlBuilder.append(String.join(", ", queryColumns)).append(" FROM ").append(tableName).append(" ");
        for (String join : joins) {
            sqlBuilder.append(join).append(" ");
        }
        if (!querySqlChainOperate.getSqlChains().isEmpty()) {
            sqlBuilder.append("WHERE ");
            buildSqlChainSql(sqlBuilder, querySqlChainOperate);
        }
        if (!groupByColumns.isEmpty()) {
            sqlBuilder.append("GROUP BY ").append(String.join(", ", groupByColumns)).append(" ");
        }
        if (!havingSqlChainOperate.getSqlChains().isEmpty()) {
            sqlBuilder.append("HAVING ");
            buildSqlChainSql(sqlBuilder, havingSqlChainOperate);
        }
        if (!orderByColumns.isEmpty()) {
            sqlBuilder.append("ORDER BY ").append(String.join(", ", orderByColumns)).append(" ");
        }
        return formatEndingSql(sqlBuilder);
    }

    /**
     * 获取查询SQL参数
     * @return SQL参数
     */
    public Object[] getSelectSqlParams() {
        return Stream.of(querySqlChainOperate, havingSqlChainOperate).flatMap(sqlChain -> sqlChain.getSqlParams().stream()).toArray();
    }

    /**
     * 获取更新SQL
     * @return 更新SQL
     */
    public String getUpdateSql() {
        if (StringUtils.isBlank(tableName)) {
            throw new JdbcException("Table Name Not Set Yet.");
        }
        StringBuilder sqlBuilder = new StringBuilder("UPDATE ").append(tableName).append(" SET ");
        List<SqlChain> setSqlChains = setSqlChainOperate.getSqlChains().stream().filter(this::isNotRelationSqlChain).collect(Collectors.toList());
        if (setSqlChains.isEmpty()) {
            throw new JdbcException("Set Columns Not Set Yet.");
        }
        sqlBuilder.append(setSqlChains.stream().map(SqlChain::getSql).collect(Collectors.joining(", ")));
        if (!querySqlChainOperate.getSqlChains().isEmpty()) {
            sqlBuilder.append(" WHERE ");
            buildSqlChainSql(sqlBuilder, querySqlChainOperate);
        }
        return formatEndingSql(sqlBuilder);
    }

    /**
     * 获取更新SQL参数
     * @return SQL参数
     */
    public Object[] getUpdateSqlParams() {
        return Stream.of(setSqlChainOperate, querySqlChainOperate).flatMap(sqlChain -> sqlChain.getSqlParams().stream()).toArray();
    }

    /**
     * 获取删除SQL
     * @return 更新SQL
     */
    public String getDeleteSql() {
        if (StringUtils.isBlank(tableName)) {
            throw new JdbcException("Table Name Not Set Yet.");
        }
        StringBuilder sqlBuilder = new StringBuilder("DELETE FROM ").append(tableName);
        if (!querySqlChainOperate.getSqlChains().isEmpty()) {
            sqlBuilder.append(" WHERE ");
            buildSqlChainSql(sqlBuilder, querySqlChainOperate);
        }
        return formatEndingSql(sqlBuilder);
    }

    /**
     * 获取删除SQL参数
     * @return SQL参数
     */
    public Object[] getDeleteSqlParams() {
        return querySqlChainOperate.getSqlParams().toArray();
    }

    // Run Query Sql Method
    /**
     * 查询基础数据
     * @param clazz 回调类型
     * @return 查询结果
     * @param <T> 回调类类型
     */
    public <T> T query(Class<T> clazz) {
        return jdbcTemplate.query(getSelectSql(), getSelectSqlParams(), clazz);
    }

    /**
     * 运行查询基础数据
     * @param callback 回调处理
     * @param <T> 回调类类型
     * @return 列表数据
     */
    public <T> T query(ResultSetRowCallback<T> callback) {
        return jdbcTemplate.query(getSelectSql(), getSelectSqlParams(), callback);
    }

    /**
     * 查询单条数据
     * @param clazz 回调类型
     * @return 查询结果
     * @param <T> 回调类类型
     */
    public <T> T queryForObject(Class<T> clazz) {
        return jdbcTemplate.queryForObject(getSelectSql(), getSelectSqlParams(), clazz);
    }

    /**
     * 查询列表数据
     * @param clazz 回调类型
     * @return 列表数据
     * @param <T> 回调类类型
     */
    public <T> List<T> queryForList(Class<T> clazz) {
        return jdbcTemplate.queryForList(getSelectSql(), getSelectSqlParams(), clazz);
    }

    /**
     * 查询列表数据
     * @param callback 回调处理
     * @param <T> 回调类类型
     * @return 列表数据
     */
    public <T> List<T> queryForList(ResultSetRowCallback<T> callback) {
        return jdbcTemplate.queryForList(getSelectSql(), getSelectSqlParams(), callback);
    }

    /**
     * 运行查询列表数据
     * @param clazz 回调类
     * @param <T> 回调类类型
     * @return 列表数据
     */
    public <T> List<T> queryForObjectList(Class<T> clazz) {
        return jdbcTemplate.queryForObjectList(getSelectSql(), getSelectSqlParams(), clazz);
    }

    /**
     * 运行查询列表数据
     * @return 列表数据
     */
    public Map<String, Object> queryForMap() {
        return jdbcTemplate.queryForMap(getSelectSql(), getSelectSqlParams());
    }

    /**
     * 运行查询列表数据
     * @return 列表数据
     */
    public List<Map<String, Object>> queryForMapList() {
        return jdbcTemplate.queryForMapList(getSelectSql(), getSelectSqlParams());
    }

    /**
     * 运行查询列表数据
     * @param clazz 回调类
     * @param <T> 回调类类型
     * @return 列表数据
     */
    public <T> Stream<T> queryForObjectStream(Class<T> clazz) {
        return jdbcTemplate.queryForObjectStream(getSelectSql(), getSelectSqlParams(), clazz);
    }

    /**
     * 运行查询列表数据
     * @param clazz 回调类
     * @param <T> 回调类类型
     * @return 列表数据
     */
    public <T> Stream<T> queryForStream(Class<T> clazz) {
        return jdbcTemplate.queryForStream(getSelectSql(), getSelectSqlParams(), clazz);
    }

    /**
     * 运行查询列表数据
     * @param callback 回调处理
     * @param <T> 回调类类型
     * @return 列表数据
     */
    public <T> Stream<T> queryForStream(ResultSetRowCallback<T> callback) {
        return jdbcTemplate.queryForStream(getSelectSql(), getSelectSqlParams(), callback);
    }

    /**
     * 运行查询列表数据
     * @return 列表数据
     */
    public Row queryForRow() {
        return jdbcTemplate.queryForRow(getSelectSql(), getSelectSqlParams());
    }

    /**
     * 运行查询列表数据
     * @return 列表数据
     */
    public List<Row> queryForRowList() {
        return jdbcTemplate.queryForRowList(getSelectSql(), getSelectSqlParams());
    }

    /**
     * 查询条数
     * @param column 列名
     * @return 条数
     */
    public Long count(String column) {
        queryColumns.clear();
        queryColumns.add("count(" + column + ")");
        return jdbcTemplate.query(getSelectSql(), getSelectSqlParams(), Long.class);
    }

    /**
     * 获取查询结果条数
     * @return 条数
     */
    public Long count() {
        return count("*");
    }

    /**
     * 判断查询结果是否存在
     * @return 是否存在
     */
    public boolean exist() {
        return Optional.ofNullable(count()).orElse(0L) > 0;
    }

    // Run Update Sql Method
    /**
     * 数据更新的方法
     * @return 更新的数量
     */
    public int update() {
        return jdbcTemplate.execute(getUpdateSql(), getUpdateSqlParams());
    }

    // Run Delete Sql Method
    /**
     * 数据更新的方法
     * @return 更新的数量
     */
    public int delete() {
        return jdbcTemplate.execute(getDeleteSql(), getDeleteSqlParams());
    }

    /**
     * 构建SQL
     * @param sqlBuilder SQL构造器
     * @param querySqlChainOperate 查询条件
     */
    private void buildSqlChainSql(StringBuilder sqlBuilder, DefaultSqlChainOperate querySqlChainOperate) {
        SqlChain lastSqlChain = null;
        for (SqlChain sqlChain : querySqlChainOperate.getSqlChains()) {
            if (lastSqlChain != null) {
                if (isNotRelationSqlChain(lastSqlChain) && isNotRelationSqlChain(sqlChain)) {
                    sqlBuilder.append(SqlChainOperate.AND).append(" ");
                }
            } else {
                if (!isNotRelationSqlChain(sqlChain)) {
                    continue;
                }
            }
            sqlBuilder.append(sqlChain.getSql()).append(" ");
            lastSqlChain = sqlChain;
        }
    }

    /**
     * 判断是否不是关联条件
     * @param sqlChain SQL条件
     * @return 是否不是关联条件
     */
    private boolean isNotRelationSqlChain(SqlChain sqlChain) {
        if (sqlChain == SqlChainOperate.AND_SQL_CHAIN || sqlChain == SqlChainOperate.OR_SQL_CHAIN) {
            return false;
        }
        String sql = sqlChain.getSql().trim();
        return !sql.equalsIgnoreCase(SqlChainOperate.AND) && !sql.equalsIgnoreCase(SqlChainOperate.OR);
    }

    /**
     * 格式化结束的SQL
     * @param sqlBuilder SQL构造器
     * @return 格式化结束的SQL
     */
    private String formatEndingSql(StringBuilder sqlBuilder) {
        if (!StringUtils.isBlank(lastSql)) {
            sqlBuilder.append(lastSql);
        }
        if (sqlBuilder.charAt(sqlBuilder.length() - 1) == ' ') {
            sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
        }
        if (sqlBuilder.charAt(sqlBuilder.length() - 1) != ';') {
            sqlBuilder.append(";");
        }
        return sqlBuilder.toString();
    }
}
